package com.xyzk.diagnose.tools;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.xyzk.diagnose.TApplication;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DbAdapter {
	private static final String TAG = "DbAdapter";
	private static final int db_version = 1;

	private DbOpenHelper dbhelper = null;
	private SQLiteDatabase m_db = null;
	private Context m_context = null;
	//构造方法
	public DbAdapter()
	{
		dbhelper = new DbOpenHelper();
	}
	public void close()
	{
		if(m_db != null) m_db.close();
	}
	public void open() throws SQLiteException {
		try{
			m_db = dbhelper.getWritableDatabase();
			if(Common.Debug) Log.i(TAG,"Write And Read database!");
		}
		catch(SQLiteException ex)
		{
			m_db = dbhelper.getReadableDatabase();
			if(Common.Debug) Log.e(TAG,"only read database!");
		}
	}
	public void StartAddMost()	//开始大批量事务
	{
		String create_sql = "begin transaction";
		m_db.execSQL(create_sql);
	}
	public void StopAddMost()		//结束大批量事务
	{
		String create_sql = "commit transaction";
		m_db.execSQL(create_sql);
	}
	//更新主表
	public void addmainTable(ContentValues value,boolean update)
	{
//		if(update)
//			value.put(TableMain.update, 1);
//		else
//			value.put(TableMain.update, 0);
		if(m_db != null)
			m_db.insert(TableMain.Name, null, value);
	}
//	//更新是否上传标记
//	public boolean UpdataTableUpdate(String table,String Pvin,int Ptestnum,int update)
//	{
//		if(m_db == null) return false;
//		ContentValues cv = new ContentValues();
//		cv.put(TableMain.update, update + "");
//		cv.put(TableMain.mft, "G5");
//		m_db.update(table, cv, TableMain.vin + " like ? and " + TableMain.testnum + "=?",new String[]{Pvin,"" + Ptestnum});
//		//	m_db.update(table, values, whereClause, whereArgs)
//		return true;
//	}

	public int CreateTableData(String tbname)  //根据车型创建表  并返回测试次数
	{
		int testnum = 0;
		if(m_db == null)
		{
			if(Common.Debug) Log.e(TAG,"Database not open!");
			return -1;
		}
		//根据车型创建子表，如果已经存在该表，则不会再次创建，也不会覆盖原先的数据
		String create_sql = "CREATE TABLE IF NOT EXISTS "+
				tbname		 			+"(" +
				TableData.ID  			+" integer primary key autoincrement,"+
				TableData.testnum		+" integer," +
				TableData.testcode   	+" integer," +
				TableData.context	  	+" varchar(50)," +
				TableData.testdata 		+" varchar(50)," +
				TableData.stand			+" varchar(50)," +
				TableData.result  		+" varchar(10)" + ")";
		m_db.execSQL(create_sql);
		//先查询
		Cursor csr = null;
		csr = m_db.rawQuery("select * from " + tbname, null);
		if(csr.moveToLast())
			testnum = csr.getInt(csr.getColumnIndex(TableData.testnum));
		if(csr != null) csr.close();
		testnum ++;
		return testnum;
	}

	// 添加子表数据
	public void addDatatotable(String tbname,ContentValues value)
	{
		if(m_db != null)
			m_db.insert(tbname, null, value);
	}
	//查询详细数据表
	public List<Map<String, String>> queryDataTable(String table,String where,String[] whereArgs,
													String orderBy,String limit)
	{
		try{
			if(m_db == null)
			{
				if(Common.Debug) Log.e(TAG,"Database not open!");
				return null;
			}
			Cursor csr = null;
			csr = m_db.query(table, null, where, whereArgs,
					null, null, orderBy,limit);
			List<Map<String,String>> lst = new ArrayList<Map<String,String>>();
			while(csr != null && csr.moveToNext())
			{
				HashMap<String, String> map = new HashMap<String, String>();
				map.put("SHOW_ID", csr.getString(csr.getColumnIndex(TableData.testcode)));
				map.put("SHOW_CONTEXT", csr.getString(csr.getColumnIndex(TableData.context)));
				String data = csr.getString(csr.getColumnIndex(TableData.testdata));
				//if(data.length() > 0)
				for(int s = 0; s < data.length(); s ++)
				{
					byte [] databyte = data.getBytes();
					if(databyte[s] == 0)
						databyte[s] = '-';
					data = new String(databyte);
				}
				map.put("SHOW_RESULT", data);
				map.put("SHOW_TIME", csr.getString(csr.getColumnIndex(TableData.result)));
				lst.add(map);
			}
			if(csr != null) csr.close();
			return lst;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return null;
	}
//	//查询主表
//	public List<Map<String, Object>> querymainTable(String where,String[] whereArgs,
//													String orderBy,String limit)
//	{
//		try{
//			if(m_db == null)
//			{
//				if(Common.Debug) Log.e(TAG,"Database not open!");
//				return null;
//			}
//			Cursor csr = null;
//			csr = m_db.query(TableMain.Name, null, where, whereArgs,
//					null, null, orderBy,limit);
//			List<Map<String,Object>> lst = new ArrayList<Map<String,Object>>();
//			while(csr != null && csr.moveToNext())
//			{
//				HashMap<String, Object> map = new HashMap<String, Object>();
//				map.put(TableMain.vin, csr.getString(csr.getColumnIndex(TableMain.vin)));
//				map.put(TableMain.cartype, csr.getString(csr.getColumnIndex(TableMain.cartype)));
//				map.put(TableMain.mft, csr.getString(csr.getColumnIndex(TableMain.mft)));
//				map.put(TableMain.timestart, csr.getString(csr.getColumnIndex(TableMain.timestart)));
//				map.put(TableMain.timeend, csr.getString(csr.getColumnIndex(TableMain.timeend)));
//				map.put(TableMain.result, csr.getString(csr.getColumnIndex(TableMain.result)));
//				map.put(TableMain.printdata, csr.getString(csr.getColumnIndex(TableMain.printdata)));
//				map.put(TableMain.device, csr.getString(csr.getColumnIndex(TableMain.device)));
//				map.put(TableMain.team, csr.getString(csr.getColumnIndex(TableMain.team)));
//				map.put(TableMain.testnum, csr.getInt(csr.getColumnIndex(TableMain.testnum)));
//				map.put(TableMain.ID, csr.getInt(csr.getColumnIndex(TableMain.ID)));
//				lst.add(map);
//			}
//			if(csr != null) csr.close();
//			return lst;
//		}
//		catch(Exception ex)
//		{
//			ex.printStackTrace();
//		}
//		return null;
//	}
	/**
	 * 根据表名，条件从详细数据表查询数据
	 * @param table 表名
	 * @param where 条件
	 * @param whereArgs 条件参数
	 * @param orderBy  排序
	 * @param limit  限制条件
	 * @return 返回查找的所有数据
	 */
	public List<Map<String, String>> queryDataFromTable(String table){

		try{
			if(m_db == null) return null;
			// 修改查询规则
			Cursor csr = m_db.query(table, null, null, null,null, null, null, null);
			List<Map<String,String>> list = new ArrayList<Map<String,String>>();
			while(csr != null && csr.moveToNext()){
				HashMap<String, String> map = new HashMap<String, String>();
				map.put(Common.ID, csr.getString(csr.getColumnIndex(TableData.ID)));
				map.put(Common.TESTNAME, csr.getString(csr.getColumnIndex(TableData.testdata)));
				map.put(Common.CONTENT, csr.getString(csr.getColumnIndex(TableData.context)));
				map.put(Common.RESULT, csr.getString(csr.getColumnIndex(TableData.result)));
				list.add(map);
			}
			if(csr != null) csr.close();
			return list;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	/**
	 * 查询数据库主表所有未上传的数据
	 * service服务自动补传使用
	 */

	public List queryMainTableUnupdate(){
		try {
			if (m_db == null) return null;
			String querymainSql = "select "+
					TableMain.ID 			+"," +
					TableMain.vin			+"," +
					TableMain.car			+"," +
					TableMain.cartype		+"," +
					TableMain.carname		+"," +
					TableMain.carstation	+"," +
					TableMain.esk			+"," +
					TableMain.pin			+"," +
					TableMain.printnum		+"," +
					TableMain.printdata		+"," +
					TableMain.device		+"," +
					TableMain.testnum		+"," +
					TableMain.timestart		+"," +
					TableMain.timeend		+"," +
					TableMain.logfileName	+"," +
					TableMain.logpath		+"," +
					TableMain.update
					+" from "+TableMain.Name + " where " +
					TableMain.update + "='未上传'";
			List querymainlist = (List) m_db.rawQuery(querymainSql,null);
			return querymainlist;
		}catch (Exception e){
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 根据主表数据查询子表的数据
	 * service层需要
	 */
	public List querysubdataforvin(String vin,int Testnum){
		try {
			if (m_db == null) return null;
			String querysubdataforvin = "select "+
					TableData.ID			+"," +
					TableData.testcode		+"," +
					TableData.context		+"," +
					TableData.testdata		+"," +
					TableData.stand			+"," +
					TableData.result		+"," +
					TableData.testnum
					+ " from " + vin + " where " +TableData.testnum +"=" +Testnum;
			List querysubdatalist = (List) m_db.rawQuery(querysubdataforvin,null);
			return querysubdatalist;
		}catch (Exception e){
			e.printStackTrace();
		}
		return null;
	}

	/**
	 *
	 * 修改数据库的上传状态
	 * service补传数据时使用
	 */
	public boolean updatemaintable(int id){
		try{
			if (m_db==null) return false;
			String updatemainSql ="update " +TableMain.Name + " set " +TableMain.update + " = '已上传'  where " + TableMain.ID +" = " +id;
			m_db.execSQL(updatemainSql);
			return true;
		}catch (Exception e){
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 根据条件从主表中查询数据
	 * @param where 查询条件
	 * @param whereArgs 条件参数
//	 * @param orderBy 排序
//	 * @param limit   限制条件
	 * @return 返回查询到的总数据
	 */
	public List<Map<String, String>> queryMainTable(String where,String[] whereArgs){
		try{
			if(m_db == null) return null;
			Cursor csr=m_db.query(TableMain.Name, null, where, whereArgs, null, null, null, null);
			List<Map<String, String>> list = new ArrayList<Map<String, String>>();
			while(csr != null && csr.moveToNext()){
				HashMap<String, String> map = new HashMap<String, String>();

				map.put(TableMain.ID, csr.getInt(csr.getColumnIndex(TableMain.ID)) + "");
				map.put(TableMain.car,csr.getString(csr.getColumnIndex(TableMain.car)));
				map.put(TableMain.vin, csr.getString(csr.getColumnIndex(TableMain.vin)));
				map.put(TableMain.cartype, csr.getString(csr.getColumnIndex(TableMain.cartype)));
				map.put(TableMain.carname, csr.getString(csr.getColumnIndex(TableMain.carname)));
				map.put(TableMain.carstation,csr.getString(csr.getColumnIndex(TableMain.carstation)));
				map.put(TableMain.printnum,csr.getInt(csr.getColumnIndex(TableMain.printnum))+"");
				map.put(TableMain.esk,csr.getString(csr.getColumnIndex(TableMain.esk)));
				map.put(TableMain.pin,csr.getString(csr.getColumnIndex(TableMain.pin)));
				map.put(TableMain.device,csr.getString(csr.getColumnIndex(TableMain.device)));
				map.put(TableMain.logfileName,csr.getString(csr.getColumnIndex(TableMain.logfileName)));
				map.put(TableMain.logpath,csr.getString(csr.getColumnIndex(TableMain.logpath)));
				map.put(TableMain.result, csr.getString(csr.getColumnIndex(TableMain.result)));
				map.put(TableMain.printdata, csr.getString(csr.getColumnIndex(TableMain.printdata)));
				map.put(TableMain.timestart, csr.getString(csr.getColumnIndex(TableMain.timestart)));
				map.put(TableMain.timeend,csr.getString(csr.getColumnIndex(TableMain.timeend)));
				map.put(TableMain.testnum, csr.getInt(csr.getColumnIndex(TableMain.testnum)) + "");
				map.put(TableMain.update, csr.getString(csr.getColumnIndex(TableMain.update)));

				list.add(map);
			}
			if(csr != null) csr.close();
			return list.size() == 0 ? null : list;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	//查询主表,返回测试次数
	public int QueryMainTestNum(String vin)
	{
		if(m_db == null)
		{
			if(Common.Debug) Log.e(TAG,"Database not open!");
			return -1;
		}
		int testnum = 0;
		Cursor csr = null;
		csr = m_db.query(TableMain.Name, null, TableMain.vin + "=?", new String[]{vin},
				null, null, null,null);
		while(csr != null && csr.moveToNext())
		{
			if(csr.getInt(csr.getColumnIndex(TableMain.testnum)) > testnum)
				testnum = csr.getInt(csr.getColumnIndex(TableMain.testnum));
		}
		if(csr != null) csr.close();
		return testnum;
	}
	private static class DbOpenHelper extends SQLiteOpenHelper{

		public DbOpenHelper() {
			super(TApplication.getContext(), Common.db_name, null, db_version);
		}
		@Override
		public void onCreate(SQLiteDatabase db) {
			//创建主表
			String create_sql = "CREATE TABLE "+
					TableMain.Name 			+"(" +
					TableMain.ID  			+" integer primary key autoincrement,"+
					TableMain.vin  			+" varchar(20)," +
					TableMain.car			+" varchar(20)," +
					TableMain.cartype   	+" varchar(30)," +
					TableMain.carstation   	+" varchar(30)," +
					TableMain.carname   	+" varchar(30)," +
					TableMain.esk			+" varchar(30)," +
					TableMain.pin			+" varchar(30)," +
					TableMain.timestart  	+" varchar(20)," +
					TableMain.timeend  		+" varchar(20)," +
					TableMain.device  		+" varchar(12)," +
					TableMain.printnum		+" varchar(10)," +
					TableMain.testnum  		+" integer," +
					TableMain.result  		+" varchar(10)," +
					TableMain.printdata  	+" varchar(200)," +
					TableMain.logpath  		+" varchar(30)," +
					TableMain.logfileName	+" varchar(30)," +
					TableMain.update        +" varchar(30)" + ")";
			db.execSQL(create_sql);

		}
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub

		}
	}
	public interface TableMain{
		String Name = "tablemain";  //表名
		String ID = "id";			//id
		String vin = "vin";		//vin码
		String car = "car";     	//	车型
		String cartype = "cartype";	// 车型代码
		String carstation = "carsation"; // 选择检测工位
		String carname = "carname";	//车型名称
		String timestart = "timestart";	//开始检测时间
		String timeend = "timeend";		//结束检测时间
		String device = "device";		//设备号
		String printnum = "printnum";	//打印机设备编号
		String testnum = "testnum";		//测试次数
		String result = "result" ; 		//检测结果
		String printdata = "printdata";	//打印内容
		String pin ="pin" ; 			//车型pin码
		String esk="esk";				//车辆esk码
		String logfileName="logfilename"; // 日志文件名
		String logpath = "logpath";   //日志文件路径
		String update = "m_update";		//是否上传
	}
	public interface TableData{
		String Name = "vin";	//以vin码作为存储名称
		String ID = "m_id";		//id
		String testnum = "testnum";		//测试序号,第几次
		String testcode = "testcode";	//测试编号
		String context = "context";		//测试内容
		String testdata = "testdata";	//测试数据结果 ：也就是返回OBD返回回来显示在界面上的数据
		String stand = "stand" ; 		//参考值
		String result = "result";		//单项检测结果
	}
}
